<?php
$servername = "localhost";
$username = "root"; // replace with your MySQL username
$password = ""; // replace with your MySQL password
$dbname = "test_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Read values sent via POST from DataTables
$draw = $_POST['draw'];
$start = $_POST['start'];
$length = $_POST['length'];
$searchValue = $_POST['search']['value'];

// Order
$orderColumn = $_POST['order'][0]['column'];
$orderDir = $_POST['order'][0]['dir'];
$orderColumnName = $_POST['columns'][$orderColumn]['data'];

// Search
$searchQuery = "";
if ($searchValue != '') {
    $searchQuery = " AND (name LIKE '%".$searchValue."%' OR age LIKE '%".$searchValue."%')";
}

// Total number of records without filtering
$sql = "SELECT COUNT(*) as total FROM users";
$result = $conn->query($sql);
$totalRecords = $result->fetch_assoc()['total'];

// Total number of records with filtering
$sql = "SELECT COUNT(*) as total FROM users WHERE 1=1 ".$searchQuery;
$result = $conn->query($sql);
$totalRecordsWithFilter = $result->fetch_assoc()['total'];

// Fetch records
$sql = "SELECT id, name, age FROM users WHERE 1=1 ".$searchQuery." ORDER BY ".$orderColumnName." ".$orderDir." LIMIT ".$start.", ".$length;
$result = $conn->query($sql);

$data = array();
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

// Prepare response
$response = array(
    "draw" => intval($draw),
    "recordsTotal" => intval($totalRecords),
    "recordsFiltered" => intval($totalRecordsWithFilter),
    "data" => $data
);

echo json_encode($response);

$conn->close();
?>